﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace AttributesFramework.Mapper
{
    public class Data
    {
        public static Dictionary<int, SqlTransaction> _currentTransactions = new Dictionary<int, SqlTransaction>();

        public static SqlTransaction getCurrentTransaction()
        {
            lock (_currentTransactions)
            {
                if (!_currentTransactions.ContainsKey(System.Threading.Thread.CurrentThread.GetHashCode()))
                {
                    SqlConnection _sqlConnection = AttributesFramework.Data.Provider.CreateConnection();
                    _currentTransactions[System.Threading.Thread.CurrentThread.GetHashCode()] = _sqlConnection.BeginTransaction();
                }
            }

            return _currentTransactions[System.Threading.Thread.CurrentThread.GetHashCode()];
        }

        public static void endCurrentTransaction(bool p_Commit)

        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();
            SqlConnection _sqlConnection = _sqlTransaction.Connection;

            if (p_Commit)
                _sqlTransaction.Commit();
            else
                _sqlTransaction.Rollback();

            _sqlConnection.Close();
            _currentTransactions.Remove(System.Threading.Thread.CurrentThread.GetHashCode());
        }

        public static void Map(Type p_ClassType)
        {
            Util.clearMappedTypes();

            SqlTransaction _sqlTransaction = getCurrentTransaction();

            lock (_sqlTransaction)
            {
                try
                {
                    Map(p_ClassType, _sqlTransaction);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }
        }

        public static void Map(Type p_ClassType, SqlTransaction p_Transaction)
        {
            if (Util.setMappedType(p_ClassType))
                return;

            if (Util.ExistMap(p_ClassType, p_Transaction))
                return;

            if (Attributes.GetDBColumnsPrimaryKeys(p_ClassType).Count == 0)
                throw new Exceptions.DBColumnPrimaryKeyNotFoundException();

            AttributesFramework.Attributes.DBTableAttribute _DBTableAttribute = Attributes.GetDBTableAttribute(p_ClassType);

            string _createTableQuery = "CREATE TABLE " + _DBTableAttribute.Name + "(";

            bool _isFirstColumn = true;
            foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnAttributes(p_ClassType))
            {
                if (!_isFirstColumn)
                    _createTableQuery += ", ";

                if (_dbColumnAttribute.Size > 0)
                    _createTableQuery += _dbColumnAttribute.Name + " " + _dbColumnAttribute.DataType + "(" + _dbColumnAttribute.Size + ")";
                else
                    _createTableQuery += _dbColumnAttribute.Name + " " + _dbColumnAttribute.DataType;

                if (_dbColumnAttribute.AllowNull == false)
                    _createTableQuery += " NOT NULL";

                if (_dbColumnAttribute.DefaultValue != null)
                    _createTableQuery += " DEFAULT " + _dbColumnAttribute.DefaultValue;

                if (_dbColumnAttribute.isAutoIncrement)
                    _createTableQuery += " IDENTITY(1,1)";

                _isFirstColumn = false;
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
            {
                if (!Util.ExistMap(_dbColumnAttribute.ClassType, p_Transaction))
                    Map(_dbColumnAttribute.ClassType, p_Transaction);

                if (!_isFirstColumn)
                    _createTableQuery += ", ";

                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                if (_pkAttribute.Size > 0)
                    _createTableQuery += _dbColumnAttribute.Name + " " + _pkAttribute.DataType + "(" + _pkAttribute.Size + ")";
                else
                    _createTableQuery += _dbColumnAttribute.Name + " " + _pkAttribute.DataType;

                _isFirstColumn = false;
            }

            if (Attributes.GetDBColumnsPrimaryKeys(p_ClassType).Count > 0)
            {
                _createTableQuery += ", PRIMARY KEY(";
                _isFirstColumn = true;
            }

            foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnsPrimaryKeys(p_ClassType))
            {
                if (!_isFirstColumn)
                    _createTableQuery += ", ";

                _createTableQuery += _dbColumnAttribute.Name;

                _isFirstColumn = false;
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelationsPrimaryKeys(p_ClassType))
            {
                if (!_isFirstColumn)
                    _createTableQuery += ", ";

                _createTableQuery += _dbColumnAttribute.Name;

                _isFirstColumn = false;
            }

            if (Attributes.GetDBColumnsPrimaryKeys(p_ClassType).Count > 0)
                _createTableQuery += ")";

            _createTableQuery += ")";

            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, _createTableQuery, null);

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.ONE_N))
            {
                if (!Util.ExistMap(_dbColumnAttribute.ClassType, p_Transaction))
                    Map(_dbColumnAttribute.ClassType, p_Transaction);

                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];

                if (_pkAttribute.Size > 0)
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _childTable.Name + " ADD " + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType + "(" + _pkAttribute.Size + ")", null);
                else
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _childTable.Name + " ADD " + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType, null);
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.N_N))
            {
                if (!Util.ExistMap(_dbColumnAttribute.ClassType, p_Transaction))
                    Map(_dbColumnAttribute.ClassType, p_Transaction);

                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                string _relationTableName = _DBTableAttribute.Name + "_" + _childTable.Name + "_" + _dbColumnAttribute.Name;

                if (!Util.ExistTable(_relationTableName, p_Transaction))
                {
                    if (_pkAttribute.Size > 0 && _childPkAttribute.Size > 0)
                    {
                        if (_childTable.Name == _DBTableAttribute.Name)
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1 " + _pkAttribute.DataType + " (" + _pkAttribute.Size + "), " + _childPkAttribute.Name + "_" + _childTable.Name + "_2 " + _childPkAttribute.DataType + "(" + _childPkAttribute.Size + "), PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1, " + _childPkAttribute.Name + "_" + _childTable.Name + "_2))", null);
                        else
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType + " (" + _pkAttribute.Size + "), " + _childPkAttribute.Name + "_" + _childTable.Name + " " + _childPkAttribute.DataType + "(" + _childPkAttribute.Size + "), PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "))", null);
                    }
                    else if (_pkAttribute.Size > 0 && _childPkAttribute.Size == 0)
                    {
                        if (_childTable.Name == _DBTableAttribute.Name)
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1 " + _pkAttribute.DataType + " (" + _pkAttribute.Size + "), " + _childPkAttribute.Name + "_" + _childTable.Name + "_2 " + _childPkAttribute.DataType + ", PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1, " + _childPkAttribute.Name + "_" + _childTable.Name + "_2))", null);
                        else
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType + " (" + _pkAttribute.Size + "), " + _childPkAttribute.Name + "_" + _childTable.Name + " " + _childPkAttribute.DataType + ", PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "))", null);
                    }
                    else if (_pkAttribute.Size == 0 && _childPkAttribute.Size > 0)
                    {
                        if (_childTable.Name == _DBTableAttribute.Name)
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1 " + _pkAttribute.DataType + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "_2 " + _childPkAttribute.DataType + "(" + _childPkAttribute.Size + "), PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1, " + _childPkAttribute.Name + "_" + _childTable.Name + "_2))", null);
                        else
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType + ", " + _childPkAttribute.Name + "_" + _childTable.Name + " " + _childPkAttribute.DataType + "(" + _childPkAttribute.Size + "), PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "))", null);
                    }
                    else
                    {
                        if (_childTable.Name == _DBTableAttribute.Name)
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1 " + _pkAttribute.DataType + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "_2 " + _childPkAttribute.DataType + ", PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1, " + _childPkAttribute.Name + "_" + _childTable.Name + "_2))", null);
                        else
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "CREATE TABLE " + _relationTableName + " (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + " " + _pkAttribute.DataType + ", " + _childPkAttribute.Name + "_" + _childTable.Name + " " + _childPkAttribute.DataType + ", PRIMARY KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ", " + _childPkAttribute.Name + "_" + _childTable.Name + "))", null);
                    }
                }
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
            {
                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _DBTableAttribute.Name + " ADD CONSTRAINT FK_" + _childTable.Name + "_" + _DBTableAttribute.Name + "_" + _childPkAttribute.Name + "_" + _dbColumnAttribute.Name + " FOREIGN KEY (" + _dbColumnAttribute.Name + ") REFERENCES " + _childTable.Name + " (" + _childPkAttribute.Name + ")", null);
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.ONE_N))
            {
                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _childTable.Name + " ADD CONSTRAINT FK_" + _childTable.Name + "_" + _DBTableAttribute.Name + "_" + _childPkAttribute.Name + "_" + _pkAttribute.Name + " FOREIGN KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ") REFERENCES " + _DBTableAttribute.Name + " (" + _pkAttribute.Name + ")", null);
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.N_N))
            {
                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                string _relationTableName = _DBTableAttribute.Name + "_" + _childTable.Name + "_" + _dbColumnAttribute.Name;

                if (_childTable.Name == _DBTableAttribute.Name)
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _relationTableName + " ADD CONSTRAINT FK_" + _relationTableName + "_" + _pkAttribute.Name + "_1 FOREIGN KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + "_1) REFERENCES " + _DBTableAttribute.Name + " (" + _pkAttribute.Name + ")", null);
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _relationTableName + " ADD CONSTRAINT FK_" + _relationTableName + "_" + _childPkAttribute.Name + "_2 FOREIGN KEY (" + _childPkAttribute.Name + "_" + _childTable.Name + "_2) REFERENCES " + _childTable.Name + " (" + _childPkAttribute.Name + ")", null);
                }
                else
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _relationTableName + " ADD CONSTRAINT FK_" + _relationTableName + "_" + _pkAttribute.Name + " FOREIGN KEY (" + _pkAttribute.Name + "_" + _DBTableAttribute.Name + ") REFERENCES " + _DBTableAttribute.Name + " (" + _pkAttribute.Name + ")", null);
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "ALTER TABLE " + _relationTableName + " ADD CONSTRAINT FK_" + _relationTableName + "_" + _childPkAttribute.Name + " FOREIGN KEY (" + _childPkAttribute.Name + "_" + _childTable.Name + ") REFERENCES " + _childTable.Name + " (" + _childPkAttribute.Name + ")", null);
                }
            }
        }

        public static void Insert(object p_Object)
        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();

            lock (_sqlTransaction)
            {
                try
                {
                    Insert(p_Object, _sqlTransaction);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }
        }

        public static void Insert(object p_Object, SqlTransaction p_Transaction)
        {
            if (p_Object == null)
                return;

            Type _classType = p_Object.GetType();

            AttributesFramework.Attributes.DBTableAttribute _classTable = Attributes.GetDBTableAttribute(_classType);
            List<AttributesFramework.Attributes.DBColumnAttribute> _primaryKeyColumns = Attributes.GetDBColumnsPrimaryKeys(_classType);

            if (_primaryKeyColumns.Count > 0)
            {
                foreach (AttributesFramework.Attributes.DBColumnAttribute _pkAttribute in _primaryKeyColumns)
                {
                    if ((!_pkAttribute.isAutoIncrement) && (Util.GetSQLValue(p_Object, _pkAttribute) == Util.GetEmptyValue(_pkAttribute.DataType)))
                        return;
                }
            }

            string _insertQuery = "INSERT INTO " + _classTable.Name + "(";

            bool _isFirstColumn = true;
            foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnAttributes(_classType))
            {
                if (!_dbColumnAttribute.isAutoIncrement && !_dbColumnAttribute.isReadOnly)
                {
                    if (!_isFirstColumn)
                        _insertQuery += ", ";

                    _insertQuery += _dbColumnAttribute.Name;

                    _isFirstColumn = false;
                }
            }

            _insertQuery += ") VALUES (";

            _isFirstColumn = true;

            foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnAttributes(_classType))
            {
                if (!_dbColumnAttribute.isAutoIncrement && !_dbColumnAttribute.isReadOnly)
                {
                    if (!_isFirstColumn)
                        _insertQuery += ", ";

                    _insertQuery += Util.GetSQLValue(p_Object, _dbColumnAttribute);

                    _isFirstColumn = false;
                }
            }

            _insertQuery += ")\nSELECT @@IDENTITY\n";

            if (_isFirstColumn)
            {
                _insertQuery = "INSERT INTO " + _classTable.Name + " DEFAULT VALUES\nSELECT @@IDENTITY\n";
            }

            if (_primaryKeyColumns.Count > 0)
            {
                AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = _primaryKeyColumns[0];

                object _dbValue = AttributesFramework.Data.Provider.ExecuteScalar(p_Transaction, CommandType.Text, _insertQuery, null).ToString();

                if (_pkAttribute.DataType == SqlDbType.Int)
                    p_Object.GetType().GetProperty(_pkAttribute.PropertyName).SetValue(p_Object, int.Parse(_dbValue.ToString()), null);
                else if (_pkAttribute.DataType == SqlDbType.UniqueIdentifier)
                    p_Object.GetType().GetProperty(_pkAttribute.PropertyName).SetValue(p_Object, new Guid(_dbValue.ToString()), null);
                else if (_pkAttribute.DataType == SqlDbType.Float)
                    p_Object.GetType().GetProperty(_pkAttribute.PropertyName).SetValue(p_Object, double.Parse(_dbValue.ToString()), null);

                foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_classType, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
                {
                    object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                    AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                    AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                    if (_value != null)
                    {
                        if (!_dbColumnAttribute.isReadOnly)
                        {
                            if (Util.GetSQLValue(_value, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                                Insert(_value, p_Transaction);
                            else
                                Update(_value, p_Transaction);
                        }

                        foreach (AttributesFramework.Attributes.DBColumnAttribute __childPkAttribute in Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType))
                        {
                            if (Util.GetSQLValue(_value, __childPkAttribute) != Util.GetEmptyValue(__childPkAttribute.DataType))
                                AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "UPDATE " + _classTable.Name + " SET " + _dbColumnAttribute.Name + " = " + Util.GetSQLValue(_value, __childPkAttribute) + " WHERE " + _pkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                        }
                    }
                }

                foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_classType, AttributesFramework.Attributes.DBRelationType.ONE_N))
                {
                    object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                    if (_value != null)
                    {
                        AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                        AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                        int _count = (int)_value.GetType().GetProperty("Count").GetValue(_value, null);

                        for (int i = 0; i < _count; i++)
                        {
                            object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                            if (!_dbColumnAttribute.isReadOnly)
                            {
                                if (Util.GetSQLValue(_child, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                                    Insert(_child, p_Transaction);
                                else
                                    Update(_child, p_Transaction);
                            }

                            if (Util.GetSQLValue(_child, _childPkAttribute) != Util.GetEmptyValue(_childPkAttribute.DataType))
                                AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "UPDATE " + _childTable.Name + " SET " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute) + " WHERE " + _childPkAttribute.Name + " = " + Util.GetSQLValue(_child, _childPkAttribute), null);
                        }
                    }
                }

                foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_classType, AttributesFramework.Attributes.DBRelationType.N_N))
                {
                    object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                    AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                    AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                    string _relationTableName = _classTable.Name + "_" + _childTable.Name + "_" + _dbColumnAttribute.Name;

                    int _count = (int)_value.GetType().GetProperty("Count").GetValue(_value, null);
                    for (int i = 0; i < _count; i++)
                    {
                        object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                        if (!_dbColumnAttribute.isReadOnly)
                        {
                            if (Util.GetSQLValue(_child, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                                Insert(_child, p_Transaction);
                            else
                                Update(_child, p_Transaction);
                        }

                        if (Util.GetSQLValue(_child, _childPkAttribute) != Util.GetEmptyValue(_childPkAttribute.DataType))
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "INSERT INTO " + _relationTableName + " VALUES (" + Util.GetSQLValue(p_Object, _pkAttribute) + ", " + Util.GetSQLValue(_child, _childPkAttribute) + ")", null);
                    }
                }
            }
        }

        public static void Update(object p_Object)
        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();

            lock (_sqlTransaction)
            {
                try
                {
                    Update(p_Object, _sqlTransaction);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }
        }

        public static void Update(object p_Object, SqlTransaction p_Transaction)
        {
            if (p_Object == null)
                return;

            Type _type = p_Object.GetType();

            AttributesFramework.Attributes.DBTableAttribute _classTable = Attributes.GetDBTableAttribute(_type);
            AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(_type)[0];

            string _updateQuery = "UPDATE " + _classTable.Name + " SET ";

            bool _isFirstColumn = true;
            foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnAttributes(_type))
            {
                if (!_dbColumnAttribute.isReadOnly && !_dbColumnAttribute.isPrimaryKey && (Watcher.isPropertyDirty(p_Object, _dbColumnAttribute.PropertyName) || Mapper.Util.isWebService()))
                {
                    if (!_isFirstColumn)
                        _updateQuery += ", ";

                    _updateQuery += _dbColumnAttribute.Name + " = " + Util.GetSQLValue(p_Object, _dbColumnAttribute);

                    _isFirstColumn = false;
                }
            }

            _updateQuery += " WHERE " + _pkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute);

            if ((Watcher.isObjectDirty(p_Object) || Mapper.Util.isWebService()) && !_isFirstColumn)
                AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, _updateQuery, null);

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
            {
                object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                if (!_dbColumnAttribute.isReadOnly)
                {
                    if (Util.GetSQLValue(_value, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                        Insert(_value, p_Transaction);
                    else
                        Update(_value, p_Transaction);
                }

                if (Util.GetSQLValue(_value, _childPkAttribute) != Util.GetEmptyValue(_childPkAttribute.DataType))
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "UPDATE " + _classTable.Name + " SET " + _dbColumnAttribute.Name + " = " + Util.GetSQLValue(_value, _childPkAttribute) + " WHERE " + _pkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                }
                else
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "UPDATE " + _classTable.Name + " SET " + _dbColumnAttribute.Name + " = NULL WHERE " + _pkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);

                    if (!_dbColumnAttribute.isReadOnly)
                        AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "DELETE FROM " + _childTable.Name + " WHERE " + _childPkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                }
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.ONE_N))
            {
                if (Watcher.isPropertyAcessed(p_Object, _dbColumnAttribute.PropertyName) || Util.isWebService())
                {
                    object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                    AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                    AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                    List<string> _childIDs = new List<string>();
                    SqlDataReader _dr;

                    if (_childTable == _classTable)
                        _dr = AttributesFramework.Data.Provider.ExecuteReader(p_Transaction, CommandType.Text, "SELECT " + _pkAttribute.Name + " FROM " + _childTable.Name + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                    else
                        _dr = AttributesFramework.Data.Provider.ExecuteReader(p_Transaction, CommandType.Text, "SELECT " + _childPkAttribute.Name + " FROM " + _childTable.Name + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);

                    while (_dr.Read())
                    {
                        _childIDs.Add(_dr[0].ToString());
                    }
                    _dr.Close();

                    for (int i = 0; i < (int)_value.GetType().GetProperty("Count").GetValue(_value, null); i++)
                    {
                        object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                        if (!_dbColumnAttribute.isReadOnly)
                        {
                            if (Util.GetSQLValue(_child, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                                Insert(_child, p_Transaction);
                            else
                                Update(_child, p_Transaction);
                        }

                        if (Util.GetSQLValue(_child, _childPkAttribute) != Util.GetEmptyValue(_childPkAttribute.DataType))
                        {
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "UPDATE " + _childTable.Name + " SET " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute) + " WHERE " + _childPkAttribute.Name + " = " + Util.GetSQLValue(_child, _childPkAttribute), null);
                            _childIDs.Remove(Util.GetSQLValue(_child, _childPkAttribute));
                        }
                    }

                    if (!_dbColumnAttribute.isReadOnly)
                    {
                        foreach (string _childID in _childIDs)
                        {
                            Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object> _primaryKeys = new Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object>();
                            _primaryKeys.Add(_childPkAttribute, int.Parse(_childID));

                            object _child = RetrieveByPrimaryKeys(p_Transaction, _primaryKeys, _value.GetType().GetGenericArguments()[0]);
                            Delete(_child, p_Transaction);
                        }
                    }
                }
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.N_N))
            {
                if (Watcher.isPropertyAcessed(p_Object, _dbColumnAttribute.PropertyName) || Util.isWebService())
                {
                    object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                    AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                    AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                    string _relationTableName = _classTable.Name + "_" + _childTable.Name + "_" + _dbColumnAttribute.Name;

                    List<string> _childIDs = new List<string>();
                    SqlDataReader _dr;

                    if (_childTable.Name == _classTable.Name)
                    {
                        _dr = AttributesFramework.Data.Provider.ExecuteReader(p_Transaction, CommandType.Text, "SELECT * FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + "_1 = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                    }
                    else
                    {
                        _dr = AttributesFramework.Data.Provider.ExecuteReader(p_Transaction, CommandType.Text, "SELECT * FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                    }

                    while (_dr.Read())
                    {
                        _childIDs.Add(_dr[1].ToString());
                    }
                    _dr.Close();

                    if (_childTable.Name == _classTable.Name)
                    {
                        AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "DELETE FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + "_1 = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                    }
                    else
                    {
                        AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "DELETE FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                    }

                    int _count = (int)_value.GetType().GetProperty("Count").GetValue(_value, null);

                    for (int i = 0; i < _count; i++)
                    {
                        object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                        if (!_dbColumnAttribute.isReadOnly)
                        {
                            if (Util.GetSQLValue(_child, _childPkAttribute) == Util.GetEmptyValue(_childPkAttribute.DataType))
                                Insert(_child, p_Transaction);
                            else
                                Update(_child, p_Transaction);
                        }

                        if (Util.GetSQLValue(_child, _childPkAttribute) != Util.GetEmptyValue(_childPkAttribute.DataType))
                        {
                            _childIDs.Remove(Util.GetSQLValue(_child, _childPkAttribute));
                            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "INSERT INTO " + _relationTableName + " VALUES (" + Util.GetSQLValue(p_Object, _pkAttribute) + ", " + Util.GetSQLValue(_child, _childPkAttribute) + ")", null);
                        }
                    }

                    if (!_dbColumnAttribute.isReadOnly)
                    {
                        foreach (string _childID in _childIDs)
                        {
                            Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object> _primaryKeys = new Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object>();
                            _primaryKeys.Add(_childPkAttribute, int.Parse(_childID));

                            object _child = RetrieveByPrimaryKeys(p_Transaction, _primaryKeys, _value.GetType().GetGenericArguments()[0]);
                            Delete(_child, p_Transaction);
                        }
                    }
                }
            }
        }

        public static void Delete(object p_Object)
        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();

            lock (_sqlTransaction)
            {
                try
                {
                    Delete(p_Object, _sqlTransaction);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }
        }

        public static void Delete(object p_Object, SqlTransaction p_Transaction)
        {
            if (p_Object == null)
                return;

            string _deleteQuery = "";
            Type _type = p_Object.GetType();

            AttributesFramework.Attributes.DBTableAttribute _classTable = Attributes.GetDBTableAttribute(_type);
            AttributesFramework.Attributes.DBColumnAttribute _pkAttribute = Attributes.GetDBColumnsPrimaryKeys(_type)[0];

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.ONE_N))
            {
                object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                int _count = (int)_value.GetType().GetProperty("Count").GetValue(_value, null);
                for (int i = 0; i < _count; i++)
                {
                    object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                    if (!_dbColumnAttribute.isReadOnly)
                    {
                        Delete(_child, p_Transaction);
                    }
                }
            }

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.N_N))
            {
                object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                AttributesFramework.Attributes.DBTableAttribute _childTable = Attributes.GetDBTableAttribute(_dbColumnAttribute.ClassType);
                AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];

                string _relationTableName = _classTable.Name + "_" + _childTable.Name + "_" + _dbColumnAttribute.Name;

                if (_childTable.Name == _classTable.Name)
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "DELETE FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + "_1 = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                }
                else
                {
                    AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, "DELETE FROM " + _relationTableName + " WHERE " + _pkAttribute.Name + "_" + _classTable.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute), null);
                }

                int _count = (int)_value.GetType().GetProperty("Count").GetValue(_value, null);
                for (int i = 0; i < _count; i++)
                {
                    object _child = _value.GetType().GetMethod("get_Item").Invoke(_value, new object[] { i });

                    if (!_dbColumnAttribute.isReadOnly)
                    {
                        Delete(_child, p_Transaction);
                    }
                }
            }

            _deleteQuery = "DELETE FROM " + _classTable.Name + " WHERE " + _pkAttribute.Name + " = " + Util.GetSQLValue(p_Object, _pkAttribute);
            AttributesFramework.Data.Provider.ExecuteNonQuery(p_Transaction, CommandType.Text, _deleteQuery, null);

            foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(_type, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
            {
                object _value = p_Object.GetType().GetProperty(_dbColumnAttribute.PropertyName).GetValue(p_Object, null);

                if (!_dbColumnAttribute.isReadOnly)
                {
                    Delete(_value, p_Transaction);
                }
            }
        }

        public static ArrayList RetrieveAll(Type p_ClassType)
        {
            return RetrieveByQuery("SELECT * FROM " + Attributes.GetDBTableAttribute(p_ClassType).Name, p_ClassType);
        }

        public static ArrayList RetrieveByQuery(string p_Query, Type p_ClassType)
        {
            return RetrieveByQuerySingleThread(p_Query, p_ClassType);
        }

        public static ArrayList RetrieveByQuery(SqlTransaction p_Transaction, string p_Query, Type p_ClassType)
        {
            return RetrieveByQuerySingleThread(p_Transaction, p_Query, p_ClassType);
        }

        public static ArrayList RetrieveByQuerySingleThread(string p_Query, Type p_ClassType)
        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();
            ArrayList _return = null;

            lock (_sqlTransaction)
            {
                try
                {
                    _return = RetrieveByQuerySingleThread(_sqlTransaction, p_Query, p_ClassType);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }

            return _return;
        }

        public static ArrayList RetrieveByQuerySingleThread(SqlTransaction p_Transaction, string p_Query, Type p_ClassType)
        {
            ArrayList _returnData = new ArrayList();

            AttributesFramework.Attributes.DBTableAttribute _DBTableAttribute = Attributes.GetDBTableAttribute(p_ClassType);
            AttributesFramework.Attributes.DBColumnAttribute _DBColumnAttributePrimaryKey = Attributes.GetDBColumnsPrimaryKeys(p_ClassType)[0];

            SqlDataReader _dataReader = AttributesFramework.Data.Provider.ExecuteReader(p_Transaction, CommandType.Text, p_Query, null);
            while (_dataReader.Read())
            {
                object _retrievedObject = p_ClassType.GetConstructors()[0].Invoke(null);

                foreach (AttributesFramework.Attributes.DBColumnAttribute _dbColumnAttribute in Attributes.GetDBColumnAttributes(p_ClassType))
                {
                    if (_dataReader[_dbColumnAttribute.Name] != System.DBNull.Value)
                    {
                        _retrievedObject.GetType().GetProperty(_dbColumnAttribute.PropertyName).SetValue(_retrievedObject, _dataReader[_dbColumnAttribute.Name], null);
                    }
                    else
                    {
                        if (_retrievedObject.GetType().GetProperty(_dbColumnAttribute.PropertyName).PropertyType.Name.Contains("Nullable"))
                        {
                            _retrievedObject.GetType().GetProperty(_dbColumnAttribute.PropertyName).SetValue(_retrievedObject, null, null);
                        }
                    }

                }

                foreach (AttributesFramework.Attributes.DBRelationAttribute _dbColumnAttribute in Attributes.GetDBRelations(p_ClassType, AttributesFramework.Attributes.DBRelationType.ONE_ONE))
                {
                    AttributesFramework.Attributes.DBColumnAttribute _childPkAttribute = Attributes.GetDBColumnsPrimaryKeys(_dbColumnAttribute.ClassType)[0];
                    object _child = _dbColumnAttribute.ClassType.GetConstructors()[0].Invoke(null);

                    if (_dataReader[_dbColumnAttribute.Name] != System.DBNull.Value)
                    {
                        _child.GetType().GetProperty(_childPkAttribute.PropertyName).SetValue(_child, _dataReader[_dbColumnAttribute.Name], null);
                        _retrievedObject.GetType().GetProperty(_dbColumnAttribute.PropertyName).SetValue(_retrievedObject, _child, null);
                    }
                }


                _returnData.Add(AttributesFramework.Util.CustomProxy.Instance(_retrievedObject));
            }
            _dataReader.Close();

            return _returnData;
        }

        public static object RetrieveByPrimaryKeys(Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object> p_PrimaryKeyAttributes, Type p_ClassType)
        {
            SqlTransaction _sqlTransaction = getCurrentTransaction();
            object _return = null;

            lock (_sqlTransaction)
            {
                try
                {
                    _return = RetrieveByPrimaryKeys(_sqlTransaction, p_PrimaryKeyAttributes, p_ClassType);

                    endCurrentTransaction(true);
                }
                catch (Exception ex)
                {
                    endCurrentTransaction(false);

                    throw ex;
                }
            }

            return _return;
        }

        public static object RetrieveByPrimaryKeys(SqlTransaction p_Transaction, Dictionary<AttributesFramework.Attributes.DBColumnAttribute, object> p_PrimaryKeyAttributes, Type p_ClassType)
        {
            string _query = "";
            object _object = p_ClassType.GetConstructors()[0].Invoke(null);

            foreach (AttributesFramework.Attributes.DBColumnAttribute _primaryKeyColumn in p_PrimaryKeyAttributes.Keys)
            {
                _object.GetType().GetProperty(_primaryKeyColumn.PropertyName).SetValue(_object, p_PrimaryKeyAttributes[_primaryKeyColumn], null);
            }

            try
            {
                bool singleKey = true;
                _query = "SELECT * FROM " + Attributes.GetDBTableAttribute(p_ClassType).Name + " WHERE ";

                foreach (AttributesFramework.Attributes.DBColumnAttribute _primaryKeyColumn in p_PrimaryKeyAttributes.Keys)
                {
                    if (!singleKey)
                        _query += "AND ";

                    _query += _primaryKeyColumn.Name + " = " + Util.GetSQLValue(_object, _primaryKeyColumn) + " ";
                    singleKey = false;
                }

                return RetrieveByQuery(p_Transaction, _query, p_ClassType)[0];
            }
            catch
            {
                return p_ClassType.GetConstructors()[0].Invoke(null);
            }
        }

    }
}
